package com.pg.excel;

import com.pg.excel.bean.DidBean;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class ReadExcel {
    private static ReadExcel ourInstance = new ReadExcel();

    public static ReadExcel getInstance() {
        return ourInstance;
    }

    private ReadExcel() {
    }

    public List<DidBean> readExcelDids(String path){
        if (path.endsWith(".xlsx")){
            return readXlsxDids(path);
        }else if (path.endsWith(".csv")){
            return readCsvDids(path);
        }
        return new ArrayList<DidBean>();
    }

    private List<DidBean> readCsvDids(String path) {
        List<DidBean> result = new ArrayList<DidBean>();
        Reader reader = null;
        try {
            reader = new FileReader(path);
            CSVParser csvParser = new CSVParser(reader, CSVFormat.EXCEL);
            for (CSVRecord record : csvParser) {
                DidBean bean = new DidBean();
                String did = record.get(0);
                String tempNum = record.get(1);
                try {
                    int num = Integer.parseInt(tempNum);
                    bean.setNumber(num);
                }catch (NumberFormatException e){
//                    e.printStackTrace();
                    continue;
                }
                bean.setDid(did);
                result.add(bean);
            }
            reader.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (reader != null){
                try {
                    reader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    private List<DidBean> readXlsxDids(String path){
        List<DidBean> result = new ArrayList<DidBean>();
        FileInputStream in = null;
        try {
            in = new FileInputStream(path);
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            Sheet sheet = workbook.getSheetAt(0);
            //获取总行数
            int rows = sheet.getPhysicalNumberOfRows();
            System.out.println("总行数:"+rows);
            for (int i =0;i<rows;i++){
                Row row = sheet.getRow(i);
                //获取总列数
//                int cols = row.getPhysicalNumberOfCells();
                DidBean bean = new DidBean();
                String did = row.getCell(0).getStringCellValue();
                int number = 0;
                try {
                    Cell cell = row.getCell(1);

                    switch (cell.getCellType()){
                        case STRING:
                            number = Integer.parseInt(row.getCell(1).getStringCellValue());
                            break;
                        case NUMERIC:
                            number = (int) row.getCell(1).getNumericCellValue();
                            break;
                    }
                    bean.setNumber(number);
                }catch (NumberFormatException e){
                    continue;
                }
                bean.setDid(did);
                result.add(bean);
            }
            in.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (in != null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        System.out.println("result.size:"+result.size());
        return result;
    }
}
